package tool;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.*;
import java.util.*;

import static tool.TableProperty.*;

public class ExcelTool {
    public static List<List<String>> readExcel(File file) throws IOException, BiffException {
        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);
        // Excel的页签数量
        int index = 0;
        List<List<String>> outerList=new ArrayList<List<String>>();
        // 每个页签创建一个Sheet对象
        Sheet sheet = wb.getSheet(index);
//            log.info("该文件有"+sheet.getRows()+"行");
        // sheet.getRows()返回该页的总行数
        for (int i = 0; i < sheet.getRows(); i++) {
            List<String> innerList=new ArrayList<String>();
            // sheet.getColumns()返回该页的总列数
            for (int j = 0; j < sheet.getColumns(); j++) {
                String cellinfo = sheet.getCell(j, i).getContents();
//                    if(cellinfo.isEmpty()){//一定要删除，不然空数据不被添加会对应不起来，gan
//                        continue;
//                    }
                innerList.add(cellinfo);
            }
            outerList.add(i, innerList);
        }
        return outerList;


    }

    public static int excel2NoSQL(String prefix, File f) throws IOException, BiffException {
        List<List<String>> readExcel = readExcel(f);
        Iterator<List<String>> iterator = readExcel.iterator();
        List<String> keys = new LinkedList<>();

        if(iterator.hasNext())
        {
            List<String> head = iterator.next();
            head.remove(0);
            for(Object o:head)
            {
                keys.add(String.valueOf(o));
            }
        }
        if (I_TABLE.contains(prefix))
        {
            return i2nosql(prefix,keys,iterator);
        }else
        {
            return r2nosql(prefix,keys,iterator);
        }
    }
    private static int i2nosql(String prefix, List<String> keys, Iterator<List<String>> iterator)
    {
        UIEventHandler instance = UIEventHandler.getInstance();

        int i = 0;
        while(iterator.hasNext())
        {
            List<String> next = iterator.next();
            String rowKey = next.remove(0);
            Iterator<String> keyiter = keys.iterator();
            if (next.size() != keys.size())
            {
                throw new IndexOutOfBoundsException("size not equal in " + i +"line" + getListString(next) + "--" + getListString(keys));
            }
            Iterator<String> valueiter = next.iterator();
            while(keyiter.hasNext() && valueiter.hasNext())
            {
                String k = keyiter.next();
                String v = valueiter.next();
                if(!v.isEmpty())
                {
                    instance.Put(prefix+rowKey, k,v);
//                    UIEventSender.getInstance().Log(String.format("rowKey %s, key = %s,value = %s",prefix+rowKey,k,v));
                }
            }
            instance.Put(prefix+rowKey, TYPE ,prefix);
            instance.Put(prefix+rowKey, ID ,rowKey);
//            UIEventSender.getInstance().Log(String.format("rowKey %s, key = %s,value = %s",prefix+rowKey,TYPE ,prefix));
            i++;
        }
        instance.Save();
        return i;
    }
    private static int r2nosql(String prefix, List<String> keys, Iterator<List<String>> iterator)
    {
        if (prefix.equals(SC))
        {
            return sc2nosql(keys,iterator);
        }else
        {
            return tc2nosql(keys,iterator);
        }
    }
    private static int sc2nosql(List<String> keys, Iterator<List<String>> iterator)
    {
        UIEventHandler instance = UIEventHandler.getInstance();
        Map<String, Map<String,String>> rows = new HashMap<>();
        int i = 0;
        while (iterator.hasNext())
        {
            List<String> next = iterator.next();
            String rowKey = next.remove(0);
            Iterator<String> keyiter = keys.iterator();

            Iterator<String> valueiter = next.iterator();
            Map<String,String> infoMap = new HashMap<>();
            while(keyiter.hasNext() && valueiter.hasNext())
            {
                String k = keyiter.next();
                String v = valueiter.next();
                infoMap.put(k,v);
            }
            String realK = infoMap.get(CID)+"_"+infoMap.get(TID);

            String realV = infoMap.get(SCORE);

            instance.Put(SC+rowKey,realK,realV);
            instance.Put(SC+rowKey, ID ,rowKey);
            instance.Put(SC+rowKey, TYPE ,SC);
            i++;
        }
        instance.Save();
        return i;
    }
    private static int tc2nosql(List<String> keys, Iterator<List<String>> iterator)
    {
        UIEventHandler instance = UIEventHandler.getInstance();
//        Map<String, List<String>> rows = new HashMap<>();
        int i = 0;
        while (iterator.hasNext())
        {
            List<String> next = iterator.next();
            String rowKey = next.remove(0);
            Iterator<String> keyiter = keys.iterator();

            Iterator<String> valueiter = next.iterator();
            while(keyiter.hasNext() && valueiter.hasNext())
            {
                String k = keyiter.next();
                String v = valueiter.next();
                if(!v.isEmpty())
                {
                    instance.Put(TC+rowKey, v,"");
                    UIEventSender.getInstance().Log(String.format("rowKey %s, key = %s,value = %s",TC+rowKey,k,v));
                }
            }
            instance.Put(TC+rowKey, TYPE ,TC);
            instance.Put(TC+rowKey, ID ,rowKey);
            UIEventSender.getInstance().Log(String.format("rowKey %s, key = %s,value = %s",TC+rowKey,TYPE ,TC));
            i++;
        }
        int save = instance.Save();
        Lg.info(save);
        return i;
    }
    private static String getListString(List list)
    {
        StringBuffer sb = new StringBuffer();
        for(Object o:list)
        {
            sb.append(o).append("|");
        }
        return sb.toString();
    }
}
